prompt 住院国家医保结算清单视图 VW_ZY_YBJSQD_JZB000 create or replace view VW_ZY_YBJSQD_JZB000 as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2022.04.06 created by ZYSF-20220406-001 -- linzetao 2022.11.01 增加WLTSZT(外联病案推送状态)和WLTSMC(外联病案推送状态名称)字段 ZYSF9-20220921-002 -- zhangyc 2022.12.16 增加YBTYPE, YBJKMC select A.ZYID00 as ZYID00 ,--住院ID A.BRID00 as BRID00 ,--病人ID trim(B.ZYH000) as ZYH000 ,--住院号 A.FBBH00 as FBBH00 ,--费别编号 g.YBZXLB as YBZXLB ,--医保中心类别 trim(g.YBMC00) as YBZXMC ,--医保中心名称 trim(F.BRXM00) as BRXM00 ,--病人姓名 trim(F.BRXB00) as BRXB00 ,--性别 trim(F.BRCSRQ) as BRCSRQ ,--出生日期 trim(F.BRMZ00) as BRMZ00 ,--民族 trim(F.BRZJLX) as BRZJLX ,--证件类型 trim(F.BRZJBH) as BRZJBH ,--证件编号 trim(F.AAE140) as AAE140 ,--险种类型编码 trim(F.AAE140_MC) as AAE140_MC ,--险种类型名称 trim(F.PSN_NO) as PSN_NO ,--人员编号 trim(B.RYCWH0) as RYCWH0 ,--病人住院床号 B.DQBQ00 as DQBQBH ,--当前病人住院病区 C.BMMC00 as DQBQMC ,--当前病人住院病区名称 B.DQKS00 as DQKSBH ,--当前病人住院科室 D.BMMC00 as DQKSMC ,--当前病人住院科室名称 A.JZDH00 as JZDH00 ,--结帐单号 A.CXJZDH as CXJZDH ,--冲销结账单号 A.JZRQ00 as JZRQ00 ,--结帐日期 A.JZSJ00 as JZSJ00 ,--结帐时间 decode(trim(A.YBDJH0),'0','',trim(A.YBDJH0)) as YBDJH0 ,--医保单据号 nvl(decode(trim(A.YBZYH0),'0','',trim(A.YBZYH0)),decode(trim(b.YBZYH0),'0','',trim(b.YBZYH0))) as YBZYH0, --医保住院号 a.JZY000 as JZY000 ,--结帐操作员编码 e.ZWXM00 as JZYXM0 ,--结帐操作员姓名 A.YBJKID as YBJKID ,--医保接口ID A.PJH000 as PJH000 ,--打印的票据流水号 A.HJJE00 as HJJE00 ,--合计应收金额 A.TCJJZF as TCJJZF ,--统筹基金支付 A.GRZHZF as GRZHZF ,--个人医疗帐户支付 A.ZFJE00 as ZFJE00 ,--自付金额 trim(B.DBZBM0) as DBZBM0 ,--单病种编码 trim(B.DBZMC0) as DBZMC0 ,--单病种名称 B.RYRQ00 as RYRQ00 ,--入院日期 B.RYSJ00 as RYSJ00 ,--入院时间 B.CYRQ00 as CYRQ00 ,--出院日期 B.CYSJ00 as CYSJ00 ,--出院时间 b.ZZYS00 as ZZYS00 ,--主治医生员工号 b.ZZYSXM as ZZYSXM ,--主治医生姓名 b.ZRYS00 as ZRYS00 ,--主任医生员工号 b.ZRYSXM as ZRYSXM ,--主任医生姓名 b.SXYS00 as SXYS00 ,--主管医生员工号 b.SXYSXM as SXYSXM ,--主管医生员姓名 substrb(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'0'),1,50) as JSQDBZ ,--医保结算单上传标志( 0: 未上传 1:已上传 2:已修改) substrb(decode(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'0'),'0','未上传','1','已上传','2','已修改','未知状态'),1,50) as JSQDMC ,--清单状态名称 substrb(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'1'),1,50) as JSZTBZ ,--结算状态标志( 0: 已冲销 1:正常) substrb(decode(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'1'),'0','冲销','1','正常'),1,50) as JSZTMC ,--结算名称 decode(trim(nvl(b.YBKH00,F.YBKH00)),'0','',trim(nvl(b.YBKH00,F.YBKH00))) as YBKH00 ,--医保卡号 decode(trim(nvl(b.YBID00,F.YBID00)),'0','',trim(nvl(b.YBID00,F.YBID00))) as YBID00 ,--医保ID decode(trim(F.XZQH00),'本地','',trim(F.XZQH00)) as XZQH00 ,--行政区号 nvl(g.YBSDLB,'0') as YBSDLB ,--医保属地类别；0本地、1省内、2省外 decode(nvl(g.YBSDLB,'0'),'2','省外','1','省内','本地') as YBSDMC ,--医保属地名称 decode(sign((select count(1) from BAJK_WLBARZ where ZYID00=a.ZYID00 and YXBZ00='1')),1,'1','0') as WLTSZT, --外联病案推送状态, 1已推送0未推送 decode(sign((select count(1) from BAJK_WLBARZ where ZYID00=a.ZYID00 and YXBZ00='1')),1,'已推送','未推送') as WLTSMC, --外联病案推送状态名称, 1已推送0未推送 nvl(a.YBTYPE,'0') as YBTYPE ,--医保接口类型[0或空:福建版本 1:国家基线 decode(nvl(a.YBTYPE,'0'),'旧版','1','国家基线版','5','安徽工伤','6','离休接口','7','福建新版工伤','福建本地版')as YBJKMC --医保版本名称 from ZY_JZB000 A,ZY_BRXXB0 B,BM_BMBM00 C,BM_BMBM00 D,BM_YGBM00 E,BM_BRXXB0 F,IC_YBBRLB G where A.ZYID00 = B.ZYID00 and A.BRID00 = F.BRID00 and B.FBBH00 = G.FBBH00 and B.YBLB00 = G.YBLB00 and B.DQBQ00 = C.BMBH00 and B.DQKS00 = D.BMBH00 and A.JZY000 = E.YGBH00 / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%